using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Common.Utilities;

namespace Workflows.Components.DALC
{
    /// <summary></summary>
    public class WorkflowTriggerInstanceDALC
    {
        /// <summary></summary>
        public static WorkflowTriggerInstance GetLastWorkflowTriggerInstanceByWorkflowDesign(Guid wfDesignID)
        {
            WorkflowTriggerInstance wfTriggerInstance=new WorkflowTriggerInstance();
            wfTriggerInstance.FirstStartTime = TimeUtil.DbMinTime;
            wfTriggerInstance.LastRunTime = TimeUtil.DbMinTime;
            wfTriggerInstance.NextRunTime = TimeUtil.DbMinTime;
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetLastWorkflowTriggerInstanceByWorkflowDesign";
                cmd.CommandType = CommandType.StoredProcedure;
                
                SqlParameter wfDesignIDParam=new SqlParameter("@WorkflowDesignID", wfDesignID.ToString());
                cmd.Parameters.Add(wfDesignIDParam);

                SqlDataReader reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    wfTriggerInstance.WorkflowTriggerInstanceID =
                        new Guid(reader["WorkflowTriggerInstanceID"].ToString());
                    wfTriggerInstance.TriggerComponentID = new Guid(reader["TriggerComponentID"].ToString());
                    wfTriggerInstance.WorkflowDesignID=new Guid(reader["WorkflowDesignID"].ToString());
                    wfTriggerInstance.TriggerType = (WorkflowTriggerType) int.Parse(reader["TriggerType"].ToString());
                    wfTriggerInstance.TimeSpanUnit = (TimeLapseUnit) int.Parse(reader["TimeSpanUnit"].ToString());
                    wfTriggerInstance.Interval = int.Parse(reader["Interval"].ToString());
                    wfTriggerInstance.FirstStartTime = DateTime.Parse(reader["FirstStartTime"].ToString());
                    wfTriggerInstance.LastRunTime = DateTime.Parse(reader["LastRunTime"].ToString());
                    wfTriggerInstance.NextRunTime = DateTime.Parse(reader["NextRunTime"].ToString());
                    wfTriggerInstance.IsActive = bool.Parse(reader["IsActive"].ToString());
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return wfTriggerInstance;
        }

        /// <summary></summary>
        public static WorkflowTriggerInstance GetLastWorkflowTriggerInstanceByTriggerComponentID(Guid triggerComponentID)
        {
            WorkflowTriggerInstance wfTriggerInstance = new WorkflowTriggerInstance();
            wfTriggerInstance.FirstStartTime = TimeUtil.DbMinTime;
            wfTriggerInstance.LastRunTime = TimeUtil.DbMinTime;
            wfTriggerInstance.NextRunTime = TimeUtil.DbMinTime;

            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetLastWorkflowTriggerInstanceByTriggerComponentID";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter wfDesignIDParam = new SqlParameter("@TriggerComponentID", triggerComponentID.ToString());
                cmd.Parameters.Add(wfDesignIDParam);

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    wfTriggerInstance.WorkflowTriggerInstanceID =
                        new Guid(reader["WorkflowTriggerInstanceID"].ToString());
                    wfTriggerInstance.TriggerComponentID = new Guid(reader["TriggerComponentID"].ToString());
                    wfTriggerInstance.WorkflowDesignID = new Guid(reader["WorkflowDesignID"].ToString());
                    wfTriggerInstance.TriggerType = (WorkflowTriggerType) int.Parse(reader["TriggerType"].ToString());
                    wfTriggerInstance.TimeSpanUnit = (TimeLapseUnit) int.Parse(reader["TimeSpanUnit"].ToString());
                    wfTriggerInstance.Interval = int.Parse(reader["Interval"].ToString());
                    wfTriggerInstance.FirstStartTime = DateTime.Parse(reader["FirstStartTime"].ToString());
                    wfTriggerInstance.LastRunTime = DateTime.Parse(reader["LastRunTime"].ToString());
                    wfTriggerInstance.NextRunTime = DateTime.Parse(reader["NextRunTime"].ToString());
                    wfTriggerInstance.IsActive = bool.Parse(reader["IsActive"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return wfTriggerInstance;
        }

        /// <summary></summary>
        public static List<WorkflowTriggerInstance> GetAllWorkflowTriggerInstnacesByWorkflowDesign(Guid wfDesignID)
        {
            List<WorkflowTriggerInstance> wfTriggerInstances=new List<WorkflowTriggerInstance>();
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetAllWorkflowTriggerInstnacesByWorkflowDesign";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter wfDesignIDParam = new SqlParameter("@WorkflowDesignID", wfDesignID.ToString());
                cmd.Parameters.Add(wfDesignIDParam);

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    WorkflowTriggerInstance wfTriggerInstance = new WorkflowTriggerInstance();
                    wfTriggerInstance.WorkflowTriggerInstanceID =
                        new Guid(reader["WorkflowTriggerInstanceID"].ToString());
                    wfTriggerInstance.TriggerComponentID = new Guid(reader["TriggerComponentID"].ToString());
                    wfTriggerInstance.WorkflowDesignID = new Guid(reader["WorkflowDesignID"].ToString());
                    wfTriggerInstance.TriggerType = (WorkflowTriggerType) int.Parse(reader["TriggerType"].ToString());
                    wfTriggerInstance.TimeSpanUnit = (TimeLapseUnit) int.Parse(reader["TimeSpanUnit"].ToString());
                    wfTriggerInstance.Interval = int.Parse(reader["Interval"].ToString());
                    wfTriggerInstance.FirstStartTime = DateTime.Parse(reader["FirstStartTime"].ToString());
                    wfTriggerInstance.LastRunTime = DateTime.Parse(reader["LastRunTime"].ToString());
                    wfTriggerInstance.NextRunTime = DateTime.Parse(reader["NextRunTime"].ToString());
                    wfTriggerInstance.IsActive = bool.Parse(reader["IsActive"].ToString());

                    wfTriggerInstances.Add(wfTriggerInstance);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return wfTriggerInstances;
        }

        /// <summary></summary>
        public static void AddTriggerInstnace(WorkflowTriggerInstance nextTriggerInstance)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "AddTriggerInstnace";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter triggerInstanceIDParam =
                    new SqlParameter("@WorkflowTriggerInstanceID",
                                     nextTriggerInstance.WorkflowTriggerInstanceID.ToString());
                SqlParameter triggerComponentIDParam =
                    new SqlParameter("@TriggerComponentID", nextTriggerInstance.TriggerComponentID.ToString());
                SqlParameter wfDesignIDParam =
                    new SqlParameter("@WorkflowDesignID", nextTriggerInstance.WorkflowDesignID.ToString());
                SqlParameter triggerTypeParam = new SqlParameter("@TriggerType", (int) nextTriggerInstance.TriggerType);
                SqlParameter timeSpanUnitParam=new SqlParameter("@TimeSpanUnit",(int)nextTriggerInstance.TimeSpanUnit);
                SqlParameter intervalParam=new SqlParameter("@Interval",nextTriggerInstance.Interval);
                SqlParameter startTimeParam=new SqlParameter("@FirstStartTime",nextTriggerInstance.FirstStartTime);
                SqlParameter lastRunTimeParam=new SqlParameter("@LastRunTime",nextTriggerInstance.LastRunTime);
                SqlParameter nextRunTimeParam=new SqlParameter("@NextRunTime",nextTriggerInstance.NextRunTime);
                SqlParameter isActiveParam=new SqlParameter("@IsActive",nextTriggerInstance.IsActive);
                cmd.Parameters.AddRange(new SqlParameter[]
                                            {
                                                triggerInstanceIDParam, triggerComponentIDParam, wfDesignIDParam,
                                                triggerTypeParam, timeSpanUnitParam, intervalParam, startTimeParam,
                                                lastRunTimeParam,
                                                nextRunTimeParam, isActiveParam
                                            });
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary></summary>
        public static WorkflowTriggerInstance GetTriggerInstanceByWorkflowInstance(Guid runID)
        {
            Run run = RunDALC.GetRun(runID);
            return WorkflowTriggerInstanceDALC.GetWorkflowTriggerInstance(run.TriggerInstanceID);
        }

        /// <summary></summary>
        public static WorkflowTriggerInstance GetWorkflowTriggerInstance(Guid triggerInstanceID)
        {
            WorkflowTriggerInstance wfTriggerInstance = new WorkflowTriggerInstance();
            wfTriggerInstance.FirstStartTime = TimeUtil.DbMinTime;
            wfTriggerInstance.LastRunTime = TimeUtil.DbMinTime;
            wfTriggerInstance.NextRunTime = TimeUtil.DbMinTime;

            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetWorkflowTriggerInstance";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter wfDesignIDParam = new SqlParameter("@WorkflowTriggerInstanceID", triggerInstanceID.ToString());
                cmd.Parameters.Add(wfDesignIDParam);

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    wfTriggerInstance.WorkflowTriggerInstanceID =
                        new Guid(reader["WorkflowTriggerInstanceID"].ToString());
                    wfTriggerInstance.TriggerComponentID = new Guid(reader["TriggerComponentID"].ToString());
                    wfTriggerInstance.WorkflowDesignID = new Guid(reader["WorkflowDesignID"].ToString());
                    wfTriggerInstance.TriggerType = (WorkflowTriggerType) int.Parse(reader["TriggerType"].ToString());
                    wfTriggerInstance.TimeSpanUnit = (TimeLapseUnit) int.Parse(reader["TimeSpanUnit"].ToString());
                    wfTriggerInstance.Interval = int.Parse(reader["Interval"].ToString());
                    wfTriggerInstance.FirstStartTime = DateTime.Parse(reader["FirstStartTime"].ToString());
                    wfTriggerInstance.LastRunTime = DateTime.Parse(reader["LastRunTime"].ToString());
                    wfTriggerInstance.NextRunTime = DateTime.Parse(reader["NextRunTime"].ToString());
                    wfTriggerInstance.IsActive = bool.Parse(reader["IsActive"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return wfTriggerInstance;
        }

        /// <summary></summary>
        public static void ResetTriggerInstanceForWorkflow(WorkflowDesign wfDesign)
        {
            WorkflowTriggerInstance lastWfInstance =
                WorkflowTriggerInstanceDALC.GetLastWorkflowTriggerInstanceByWorkflowDesign(wfDesign.WorkflowGuid);
            if(lastWfInstance.LastRunTime >TimeUtil.DbMinTime && lastWfInstance.LastRunTime <TimeUtil.DbMaxTime)
            {
                WorkflowEngine engine=new WorkflowEngine();
                WorkflowTriggerBase trigger = engine.GetWorkflowTrigger(wfDesign);

                lastWfInstance.IsActive = trigger.IsActive;
                lastWfInstance.TriggerComponentID = trigger.ComponentID;
                lastWfInstance.TriggerType = trigger.TriggerType;
                lastWfInstance.WorkflowDesignID = wfDesign.WorkflowGuid;

                lastWfInstance.LastRunTime = trigger.LastRunTime;
                if(lastWfInstance.LastRunTime<TimeUtil.DbMinTime)
                {
                    lastWfInstance.LastRunTime = TimeUtil.DbMinTime;
                }
                lastWfInstance.NextRunTime = trigger.NextRunTime;
                if(lastWfInstance.NextRunTime<TimeUtil.DbMinTime)
                {
                    lastWfInstance.NextRunTime = TimeUtil.DbMinTime;
                }
                lastWfInstance.TimeSpanUnit = trigger.TimeSpanUnit;
                lastWfInstance.Interval = trigger.Interval;
                lastWfInstance.FirstStartTime = trigger.StartTime;
                if(lastWfInstance.FirstStartTime<TimeUtil.DbMinTime)
                {
                    lastWfInstance.FirstStartTime = TimeUtil.DbMinTime;
                }
                WorkflowTriggerInstanceDALC.SaveTriggerInstance(lastWfInstance);
            }
            else
            {
                // no trigger instance entry, haven't run before, do nothing 
            }
        }

        /// <summary></summary>
        private static void SaveTriggerInstance(WorkflowTriggerInstance lastWfInstance)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SaveTriggerInstance";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter triggerInstanceIDParam =
                    new SqlParameter("@WorkflowTriggerInstanceID",
                                     lastWfInstance.WorkflowTriggerInstanceID.ToString());
                SqlParameter triggerComponentIDParam =
                    new SqlParameter("@TriggerComponentID", lastWfInstance.TriggerComponentID.ToString());
                SqlParameter wfDesignIDParam =
                    new SqlParameter("@WorkflowDesignID", lastWfInstance.WorkflowDesignID.ToString());
                SqlParameter triggerTypeParam = new SqlParameter("@TriggerType", (int) lastWfInstance.TriggerType);
                SqlParameter timeSpanUnitParam = new SqlParameter("@TimeSpanUnit", (int) lastWfInstance.TimeSpanUnit);
                SqlParameter intervalParam = new SqlParameter("@Interval", lastWfInstance.Interval);
                SqlParameter startTimeParam = new SqlParameter("@FirstStartTime", lastWfInstance.FirstStartTime);
                SqlParameter lastRunTimeParam = new SqlParameter("@LastRunTime", lastWfInstance.LastRunTime);
                SqlParameter nextRunTimeParam = new SqlParameter("@NextRunTime", lastWfInstance.NextRunTime);
                SqlParameter isActiveParam = new SqlParameter("@IsActive", lastWfInstance.IsActive);
                cmd.Parameters.AddRange(new SqlParameter[]
                                            {
                                                triggerInstanceIDParam, triggerComponentIDParam, wfDesignIDParam,
                                                triggerTypeParam, timeSpanUnitParam, intervalParam, startTimeParam,
                                                lastRunTimeParam,
                                                nextRunTimeParam, isActiveParam
                                            });
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
